home *** CD-ROM | disk | FTP | other *** search
drop sequence users_sequence; create sequence users_sequence; drop sequence users_sequence_backup; create sequence users_sequence_backup; drop table users; create table users ( users_id int4 NOT NULL PRIMARY KEY DEFAULT nextval('users_sequence'), date_updated timestamp NOT NULL default CURRENT_TIMESTAMP, date_created timestamp NOT NULL default CURRENT_TIMESTAMP, active int2 CHECK (active in (0,1)) DEFAULT 0, username text NOT NULL default '' , password text NOT NULL default '' , user_type int4 not null default 1 , contact_id int4 not null default 0 REFERENCES contact ON DELETE NO ACTION ON UPDATE CASCADE );drop table users_backup; create table users_backup ( backup_id int4 NOT NULL UNIQUE DEFAULT nextval('users_sequence_backup'), users_id int4 NOT NULL DEFAULT 0, date_updated timestamp NOT NULL default CURRENT_TIMESTAMP, date_created timestamp NOT NULL default CURRENT_TIMESTAMP, active int2 CHECK (active in (0,1)) DEFAULT 0, username text NOT NULL default '', password text NOT NULL default '', user_type int4 not null default 1, contact_id int4 not null default 0, error_code text NOT NULL DEFAULT '' ); drop view users_active; create view users_active as select * from users where active = 1; drop view users_deleted; create view users_deleted as select * from users where active = 0; drop view users_backup_ids; create view users_backup_ids as select distinct users_id from users_backup; drop view users_purged; create view users_purged as select * from users_backup where oid = ANY ( select max(oid) from users_backup where users_id = ANY ( select distinct users_id from users_backup where users_backup.error_code = 'purge' and NOT users_id = ANY (select users_id from users) ) group by users_id ) ; --- Generic Functions for Perl/Postgresql version 1.0 --- Copyright 2001, Mark Nielsen --- All rights reserved. --- This Copyright notice was copied and modified from the Perl --- Copyright notice. --- This program is free software; you can redistribute it and/or modify --- it under the terms of either: --- a) the GNU General Public License as published by the Free --- Software Foundation; either version 1, or (at your option) any --- later version, or --- b) the "Artistic License" which comes with this Kit. --- This program is distributed in the hope that it will be useful, --- but WITHOUT ANY WARRANTY; without even the implied warranty of --- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See either --- the GNU General Public License or the Artistic License for more details. --- You should have received a copy of the Artistic License with this --- Kit, in the file named "Artistic". If not, I'll be glad to provide one. --- You should also have received a copy of the GNU General Public License --- along with this program in the file named "Copying". If not, write to the --- Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA --- 02111-1307, USA or visit their web page on the internet at --- http://www.gnu.org/copyleft/gpl.html. -- create a method to unpurge just one item. -- create a method to purge one item. -- \i /tmp/Test/sample/users.table --------------------------------------------------------------------- drop function sql_users_insert (); CREATE FUNCTION sql_users_insert () RETURNS int4 AS ' DECLARE record1 record; oid1 int4; id int4 :=0; record_backup RECORD; BEGIN insert into users (date_updated, date_created, active) values (CURRENT_TIMESTAMP,CURRENT_TIMESTAMP, 1); -- Get the unique oid of the row just inserted. GET DIAGNOSTICS oid1 = RESULT_OID; -- Get the users id. FOR record1 IN SELECT users_id FROM users where oid = oid1 LOOP id := record1.users_id; END LOOP; -- If id is NULL, insert failed or something is wrong. IF id is NULL THEN return (-1); END IF; -- It should also be greater than 0, otherwise something is wrong. IF id < 1 THEN return (-2); END IF; -- Now backup the data. FOR record_backup IN SELECT * FROM users where users_id = id LOOP insert into users_backup (users_id, date_updated, date_created, active, error_code) values (id, record_backup.date_updated, record_backup.date_created, record_backup.active, ''insert''); END LOOP; -- Everything has passed, return id as users_id. return (id); END; ' LANGUAGE 'plpgsql'; --------------------------------------------------------------------- drop function sql_users_delete (int4); CREATE FUNCTION sql_users_delete (int4) RETURNS int2 AS ' DECLARE id int4 := 0; id_exists int4 := 0; record1 RECORD; record_backup RECORD; return_int4 int4 :=0; BEGIN -- If the id is not greater than 0, return error. id := clean_numeric($1); IF id < 1 THEN return -1; END IF; -- If we find the id, set active = 0. FOR record1 IN SELECT users_id FROM users where users_id = id LOOP update users set active=0, date_updated = CURRENT_TIMESTAMP where users_id = id; GET DIAGNOSTICS return_int4 = ROW_COUNT; id_exists := 1; END LOOP; -- If we did not find the id, abort and return -2. IF id_exists = 0 THEN return (-2); END IF; FOR record_backup IN SELECT * FROM users where users_id = id LOOP insert into users_backup (users_id, date_updated, date_created, active , username, password, user_type, contact_id ,error_code) values (record_backup.users_id, record_backup.date_updated, record_backup.date_updated, record_backup.active , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id , ''delete'' ); END LOOP; -- If id_exists == 0, Return error. -- It means it never existed. IF id_exists = 0 THEN return (-1); END IF; -- We got this far, it must be true, return ROW_COUNT. return (return_int4); END; ' LANGUAGE 'plpgsql'; --------------------------------------------------------------------- drop function sql_users_undelete (int4); CREATE FUNCTION sql_users_undelete (int4) RETURNS int2 AS ' DECLARE id int4 := 0; id_exists int4 := 0; record1 RECORD; record_backup RECORD; return_int4 int4 :=0; BEGIN -- If the id is not greater than 0, return error. id := clean_numeric($1); IF id < 1 THEN return -1; END IF; -- If we find the id, set active = 1. FOR record1 IN SELECT users_id FROM users where users_id = id LOOP update users set active=1, date_updated = CURRENT_TIMESTAMP where users_id = id; GET DIAGNOSTICS return_int4 = ROW_COUNT; id_exists := 1; END LOOP; -- If we did not find the id, abort and return -2. IF id_exists = 0 THEN return (-2); END IF; FOR record_backup IN SELECT * FROM users where users_id = id LOOP insert into users_backup (users_id, date_updated, date_created, active , username, password, user_type, contact_id ,error_code) values (record_backup.users_id, record_backup.date_updated, record_backup.date_updated, record_backup.active , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id , ''undelete'' ); END LOOP; -- If id_exists == 0, Return error. -- It means it never existed. IF id_exists = 0 THEN return (-1); END IF; -- We got this far, it must be true, return ROW_COUNT. return (return_int4); END; ' LANGUAGE 'plpgsql'; --------------------------------------------------------------------- drop function sql_users_update (int4 , text, text, int4, int4); CREATE FUNCTION sql_users_update (int4 , text, text, int4, int4) RETURNS int2 AS ' DECLARE id int4 := 0; id_exists int4 := 0; record_update RECORD; record_backup RECORD; return_int4 int4 :=0; var_2 text; var_3 text; var_4 int4; var_5 int4; BEGIN var_2 := clean_text($2); var_3 := clean_text($3); var_4 := clean_numeric($4); var_5 := clean_numeric($5); -- If the id is not greater than 0, return error. id := clean_numeric($1); IF id < 1 THEN return -1; END IF; FOR record_update IN SELECT users_id FROM users where users_id = id LOOP id_exists := 1; END LOOP; IF id_exists = 0 THEN return (-2); END IF; update users set date_updated = CURRENT_TIMESTAMP , username = var_2, password = var_3, user_type = var_4, contact_id = var_5 where users_id = id; GET DIAGNOSTICS return_int4 = ROW_COUNT; FOR record_backup IN SELECT * FROM users where users_id = id LOOP insert into users_backup (users_id, date_updated, date_created, active , username, password, user_type, contact_id, error_code) values (record_update.users_id, record_backup.date_updated, record_backup.date_updated, record_backup.active , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id, ''update'' ); END LOOP; -- We got this far, it must be true, return ROW_COUNT. return (return_int4); END; ' LANGUAGE 'plpgsql'; --------------------------------------------------------------------- drop function sql_users_copy (int4); CREATE FUNCTION sql_users_copy (int4) RETURNS int2 AS ' DECLARE id int4 := 0; id_exists int4 := 0; record1 RECORD; record2 RECORD; record3 RECORD; return_int4 int4 := 0; id_new int4 := 0; users_new int4 :=0; BEGIN -- If the id is not greater than 0, return error. id := clean_numeric($1); IF id < 1 THEN return -1; END IF; FOR record1 IN SELECT users_id FROM users where users_id = id LOOP id_exists := 1; END LOOP; IF id_exists = 0 THEN return (-2); END IF; --- Get the new id FOR record1 IN SELECT sql_users_insert() as users_insert LOOP users_new := record1.users_insert; END LOOP; -- If the users_new is not greater than 0, return error. IF users_new < 1 THEN return -3; END IF; FOR record2 IN SELECT * FROM users where users_id = id LOOP FOR record1 IN SELECT sql_users_update(users_new , clean_text(record2.username), clean_text(record2.password), clean_text(record2.user_type), clean_text(record2.contact_id)) as users_insert LOOP -- execute some arbitrary command just to get it to pass. id_exists := 1; END LOOP; END LOOP; -- We got this far, it must be true, return new id. return (users_new); END; ' LANGUAGE 'plpgsql'; ------------------------------------------------------------------ drop function sql_users_purge (); CREATE FUNCTION sql_users_purge () RETURNS int4 AS ' DECLARE record_backup RECORD; oid1 int4 := 0; return_int4 int4 :=0; deleted int4 := 0; delete_count int4 :=0; delete_id int4; BEGIN -- Now delete one by one. FOR record_backup IN SELECT * FROM users where active = 0 LOOP -- Record the id we want to delete. delete_id = record_backup.users_id; insert into users_backup (users_id, date_updated, date_created, active , username, password, user_type, contact_id ,error_code) values (record_backup.users_id, record_backup.date_updated, record_backup.date_updated, record_backup.active , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id , ''purge'' ); -- Get the unique oid of the row just inserted. GET DIAGNOSTICS oid1 = RESULT_OID; -- If oid1 less than 1, return -1 IF oid1 < 1 THEN return (-2); END IF; -- Now delete this from the main table. delete from users where users_id = delete_id; -- Get row count of row just deleted, should be 1. GET DIAGNOSTICS deleted = ROW_COUNT; -- If deleted less than 1, return -3 IF deleted < 1 THEN return (-3); END IF; delete_count := delete_count + 1; END LOOP; -- We got this far, it must be true, return the number of ones we had. return (delete_count); END; ' LANGUAGE 'plpgsql'; ------------------------------------------------------------------ drop function sql_users_purgeone (int4); CREATE FUNCTION sql_users_purgeone (int4) RETURNS int4 AS ' DECLARE record_backup RECORD; oid1 int4 := 0; record1 RECORD; return_int4 int4 :=0; deleted int4 := 0; delete_count int4 :=0; delete_id int4; purged_no int4 := 0; BEGIN delete_id := $1; -- If purged_id less than 1, return -4 IF delete_id < 1 THEN return (-4); END IF; FOR record1 IN SELECT * FROM users where active = 0 and users_id = delete_id LOOP purged_no := purged_no + 1; END LOOP; -- If purged_no less than 1, return -1 IF purged_no < 1 THEN return (-1); END IF; -- Now delete one by one. FOR record_backup IN SELECT * FROM users where users_id = delete_id LOOP insert into users_backup (users_id, date_updated, date_created, active , username, password, user_type, contact_id ,error_code) values (record_backup.users_id, record_backup.date_updated, record_backup.date_updated, record_backup.active , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id , ''purgeone'' ); -- Get the unique oid of the row just inserted. GET DIAGNOSTICS oid1 = RESULT_OID; -- If oid1 less than 1, return -2 IF oid1 < 1 THEN return (-2); END IF; -- Now delete this from the main table. delete from users where users_id = delete_id; -- Get row count of row just deleted, should be 1. GET DIAGNOSTICS deleted = ROW_COUNT; -- If deleted less than 1, return -3 IF deleted < 1 THEN return (-3); END IF; delete_count := delete_count + 1; END LOOP; -- We got this far, it must be true, return the number of ones we had. return (delete_count); END; ' LANGUAGE 'plpgsql'; ------------------------------------------------------------------------ drop function sql_users_unpurge (); CREATE FUNCTION sql_users_unpurge () RETURNS int2 AS ' DECLARE record1 RECORD; record2 RECORD; record_backup RECORD; purged_id int4 := 0; purge_count int4 :=0; timestamp1 timestamp; purged_no int4 := 0; oid1 int4 := 0; oid_found int4 := 0; highest_oid int4 := 0; BEGIN -- Now get the unique ids that were purged. FOR record1 IN select distinct users_id from users_backup where users_backup.error_code = ''purge'' and NOT users_id = ANY (select users_id from users) LOOP purged_id := record1.users_id; timestamp1 := CURRENT_TIMESTAMP; purged_no := purged_no + 1; oid_found := 0; highest_oid := 0; -- Now we have the unique id, find its latest date. FOR record2 IN select max(oid) from users_backup where users_id = purged_id and error_code = ''purge'' LOOP -- record we got the date and also record the highest date. oid_found := 1; highest_oid := record2.max; END LOOP; -- If the oid_found is 0, return error. IF oid_found = 0 THEN return (-3); END IF; -- Now we have the latest date, get the values and insert them. FOR record_backup IN select * from users_backup where oid = highest_oid LOOP insert into users_backup (users_id, date_updated, date_created, active , username, password, user_type, contact_id ,error_code) values (purged_id, record_backup.date_updated, timestamp1, record_backup.active , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id , ''unpurge'' ); -- Get the unique oid of the row just inserted. GET DIAGNOSTICS oid1 = RESULT_OID; -- If oid1 less than 1, return -1 IF oid1 < 1 THEN return (-1); END IF; insert into users (users_id, date_updated, date_created, active , username, password, user_type, contact_id) values (purged_id, timestamp1, timestamp1, record_backup.active , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id ); -- Get the unique oid of the row just inserted. GET DIAGNOSTICS oid1 = RESULT_OID; -- If oid1 less than 1, return -2 IF oid1 < 1 THEN return (-2); END IF; END LOOP; END LOOP; -- We got this far, it must be true, return how many were affected. return (purged_no); END; ' LANGUAGE 'plpgsql'; --------------------------------------------------------------------- drop function sql_users_unpurgeone (int4); CREATE FUNCTION sql_users_unpurgeone (int4) RETURNS int2 AS ' DECLARE record_id int4; record1 RECORD; record2 RECORD; record_backup RECORD; return_int4 int4 :=0; purged_id int4 := 0; purge_count int4 :=0; timestamp1 timestamp; purged_no int4 := 0; oid1 int4 := 0; oid_found int4 := 0; highest_oid int4 := 0; BEGIN purged_id := $1; -- If purged_id less than 1, return -1 IF purged_id < 1 THEN return (-1); END IF; --- Get the current timestamp. timestamp1 := CURRENT_TIMESTAMP; FOR record1 IN select distinct users_id from users_backup where users_backup.error_code = ''purge'' and NOT users_id = ANY (select users_id from users) and users_id = purged_id LOOP purged_no := purged_no + 1; END LOOP; -- If purged_no less than 1, return -1 IF purged_no < 1 THEN return (-3); END IF; -- Now find the highest oid. FOR record2 IN select max(oid) from users_backup where users_id = purged_id and error_code = ''purge'' LOOP -- record we got the date and also record the highest date. oid_found := 1; highest_oid := record2.max; END LOOP; -- If the oid_found is 0, return error. IF oid_found = 0 THEN return (-4); END IF; -- Now get the data and restore it. FOR record_backup IN select * from users_backup where oid = highest_oid LOOP -- Insert into backup that it was unpurged. insert into users_backup (users_id, date_updated, date_created, active , username, password, user_type, contact_id ,error_code) values (purged_id, timestamp1, record_backup.date_created, record_backup.active , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id , ''unpurgeone'' ); -- Get the unique oid of the row just inserted. GET DIAGNOSTICS oid1 = RESULT_OID; -- If oid1 less than 1, return -1 IF oid1 < 1 THEN return (-1); END IF; -- Insert into live table. insert into users (users_id, date_updated, date_created, active , username, password, user_type, contact_id) values (record_backup.users_id, timestamp1, record_backup.date_updated, record_backup.active , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id ); -- Get the unique oid of the row just inserted. GET DIAGNOSTICS oid1 = RESULT_OID; -- If oid1 less than 1, return -2 IF oid1 < 1 THEN return (-2); END IF; END LOOP; -- We got this far, it must be true, return how many were affected (1). return (purged_no); END; ' LANGUAGE 'plpgsql'; insert into users (users_id, date_updated, date_created, active) values (0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0); insert into users_backup (backup_id, users_id, date_updated, date_created, active, error_code) values (0, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, 'table creation'); drop function clean_text (text); CREATE FUNCTION clean_text (text) RETURNS text AS ' my $Text = shift; # Get rid of whitespace in front. $Text =~ s/^\\s+//; # Get rid of whitespace at end. $Text =~ s/\\s+$//; # Get rid of anything not text. $Text =~ s/[^ a-z0-9\\/\\`\\~\\!\\@\\#\\$\\%\\^\\&\\*\\(\\)\\-\\_\\=\\+\\\\\\|\[\\{\\]\\}\\;\\:\\''\\"\\,\\<\\.\\>\\?\\t\\n]//gi; # Replace all multiple whitespace with one space. $Text =~ s/\\s+/ /g; return $Text; ' LANGUAGE 'plperl'; -- Just do show you what this function cleans up. select clean_text (' ,./<>?aaa aa !@#$%^&*()_+| '); drop function clean_alpha (text); CREATE FUNCTION clean_alpha (text) RETURNS text AS ' my $Text = shift; $Text =~ s/[^a-z0-9_]//gi; return $Text; ' LANGUAGE 'plperl'; -- Just do show you what this function cleans up. select clean_alpha (' ,./<>?aaa aa !@#$%^&*()_+| '); drop function clean_numeric (text); CREATE FUNCTION clean_numeric (text) RETURNS int4 AS ' my $Text = shift; $Text =~ s/[^0-9]//gi; return $Text; ' LANGUAGE 'plperl'; -- Just do show you what this function cleans up. select clean_numeric (' ,./<>?aaa aa !@#$%^&*()_+| '); drop function clean_numeric (int4); CREATE FUNCTION clean_numeric (int4) RETURNS int4 AS ' my $Text = shift; $Text =~ s/[^0-9]//gi; return $Text; ' LANGUAGE 'plperl'; -- Just do show you what this function cleans up. select clean_numeric (11111); select sql_users_insert(); select sql_users_update(1,'Username1','Password1',1); select sql_users_insert(); select sql_users_update(2,'Username2','Password2',1); drop function sql_users_verify(text,text); CREATE FUNCTION sql_users_verify (text,text) RETURNS int4 AS ' DECLARE record1 record; id int4 :=0; username text; password text; active int4 := 0; BEGIN username := clean_text($1); password := clean_text($2); FOR record1 IN SELECT users_id FROM users where users.username = username and users.password = password and users.active = 1 LOOP id := record1.users_id; END LOOP; -- If id < 1 check if username exists . IF id is NULL THEN FOR record1 IN SELECT users_id,active FROM users where users.username = username LOOP id := record1.users_id; -- active := record1.active; -- If active is < 1, isn not active. -- IF active < 1 THEN return (-3); END IF; -- If id is > 0, password is incorrect. IF id > 0 THEN return (-2); END IF; END LOOP; END IF; -- If id is < 1, username does not exist. IF id < 1 THEN return (-1); END IF; -- Everything has passed, return id as users_id. return (id); END; ' LANGUAGE 'plpgsql'; select sql_users_verify('Username1','Password1'); drop function sql_users_exists(text); CREATE FUNCTION sql_users_exists (text) RETURNS int4 AS ' DECLARE record1 record; id int4 :=0; username text; password text; BEGIN username := clean_text($1); FOR record1 IN SELECT users_id FROM users where users.username = username LOOP id := record1.users_id; END LOOP; -- Everything has passed, return id as users_id. return (id); END; ' LANGUAGE 'plpgsql'; select sql_users_exists('Username1'); drop function sql_users_active(text); CREATE FUNCTION sql_users_active (text) RETURNS int4 AS ' DECLARE record1 record; id int4 :=0; username text; password text; BEGIN username := clean_text($1); FOR record1 IN SELECT users_id FROM users where users.username = username LOOP id := record1.users_id; END LOOP; -- Everything has passed, return id as users_id. return (id); END; ' LANGUAGE 'plpgsql'; select sql_users_active('Username1'); vacuum; 
